# _*_ coding:utf-8 _*_
# @File  : export_sql.py
# @Time  : 2021-09-07  07:52
# @Author: zizle

# 导出净持仓数据
import datetime

import pandas as pd

from db import FAConnection


def export_futures_price_position():
    conn = FAConnection()
    sql = 'SELECT quotes_ts,variety_en,contract,close_price,position_volume,long_position,short_position ' \
          'FROM dat_futures_price_position WHERE variety_en="Y" AND contract="Y";'
    records = conn.query(sql)

    df = pd.DataFrame(records)
    df.sort_values(by=['quotes_ts'], inplace=True)
    df['date'] = df['quotes_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    df['net_position'] = df['long_position'] - df['short_position']
    df = df[['date', 'variety_en', 'close_price', 'position_volume', 'long_position', 'short_position', 'net_position']]
    df.columns = ['日期', '品种', '收盘价', '持仓量', '20多头持仓', '20空头持仓', '净持仓']
    df.to_excel('豆油持仓.xlsx', sheet_name='豆油持仓', index=False)


if __name__ == '__main__':
    export_futures_price_position()